<?php
App::uses('FilterHelper', 'View/Helper');



class Filtros_propiedades extends FilterHelper{

	public $db							= null;
	public $tabla 						= 'propiedades p';
	public $joins 						= '';
	public $campo_precio 				= 'precio';
	public $currency					= '$';
	public $filtros_adicionales 		= array('zona','localidad','provincia','ambientes','tipo','tipo_operacion','antiguedad','region');
	public $filtros_obligatorios		= array( 'precio'=>'','pais'=>'1');
	public $mandatory_filters_string	= '';
	public $order_by_def				= 'precio';
	private $arrayMeses = array('Enero','Febrero','Marzo','Abril','Mayo','Junio','Julio','Agosto','Septiembre','Octubre','Noviembre','Diciembre');
	private $arrayDias  = array('Lunes','Martes','Miercoles','Jueves','Viernes','Sábado','Domingo');
	public $filters_list				= array(
		'region' => array(
			'filter_type' => 'items_list',
			'title' => 'Region',
			'field_denomination' => 'region',
			'id_field_on_table' => 'r.id',
			'display_field_on_table' => 'r.descripcion',
			'selected_value' => null,
			'selected' => false,
			'display_text_on_selected' => 'Region',
			'url_on_remove' => null,
			'link_display_text' => '<value>',
			'items_list' => array()
		),
		'zona' => array(
			'filter_type' => 'items_list',
			'title' => 'Zona',
			'field_denomination' => 'zona',
			'id_field_on_table' => 'z.id',
			'display_field_on_table' => 'z.descripcion',
			'selected_value' => null,
			'selected' => false,
			'display_text_on_selected' => 'Zona',
			'url_on_remove' => null,
			'link_display_text' => '<value>',
			'items_list' => array()
		),
		'ambientes' => array(
			'filter_type' => 'items_list',
			'title' => 'Cantidad de ambientes',
			'field_denomination' => 'ambientes',
			'id_field_on_table' => 'ambientes',
			'display_field_on_table' => 'ambientes',
			'selected_value' => null,
			'selected' => false,
			'display_text_on_selected' => '',
			'url_on_remove' => null,
			'link_display_text' => '<value> ambiente',
			'items_list' => array()
		),
		'tipo' => array(
			'filter_type' => 'items_list',
			'title' => 'Tipo de propiedad',
			'field_denomination' => 'tipo',
			'id_field_on_table' => 'ti.id',
			'display_field_on_table' => 'ti.descripcion',
			'selected_value' => null,
			'selected' => false,
			'display_text_on_selected' => '',
			'url_on_remove' => null,
			'link_display_text' => '<value>',
			'items_list' => array()
		),
		'tipo_operacion' => array(
			'filter_type' => 'items_list',
			'title' => 'Tipo de operación',
			'field_denomination' => 'tipo_operacion',
			'id_field_on_table' => 'tipo_operacion',
			'display_field_on_table' => 'tipo_operacion',
			'selected_value' => null,
			'selected' => false,
			'display_text_on_selected' => '',
			'url_on_remove' => null,
			'link_display_text' => '<value>',
			'items_list' => array()
		),
		'antiguedad' => array(
			'filter_type' => 'items_list',
			'title' => 'Antigüedad',
			'field_denomination' => 'antiguedad',
			'id_field_on_table' => 'antiguedad',
			'display_field_on_table' => 'antiguedad',
			'selected_value' => null,
			'selected' => false,
			'display_text_on_selected' => '',
			'url_on_remove' => null,
			'link_display_text' => '<value>',
			'items_list' => array()
		)
		/*
		'localidad' => array(
			'filter_type' => 'items_list',
			'title' => 'Localidad',
			'field_denomination' => 'localidad',
			'id_field_on_table' => 'localidad',
			'display_field_on_table' => 'localidad',
			'selected_value' => null,
			'selected' => false,
			'display_text_on_selected' => '',
			'url_on_remove' => null,
			'link_display_text' => '<value>',
			'items_list' => array()
		),
		'provincia' => array(
			'filter_type' => 'items_list',
			'title' => 'Provincia',
			'field_denomination' => 'provincia',
			'id_field_on_table' => 'provincia',
			'display_field_on_table' => 'provincia',
			'selected_value' => null,
			'selected' => false,
			'display_text_on_selected' => '',
			'url_on_remove' => null,
			'link_display_text' => '<value>',
			'items_list' => array()
		),
		*/
	);
	
	function __construct($url,$currency='$'){

		$this->db = NewADOConnection('mysql');
		$this->db->Connect('localhost', $this->dbUserName, $this->dbUserPass, $this->dbName);
		
        // Pasar a UTF-8
        $this->db->execute("SET NAMES utf8");		
		parent::__construct($url,$currency);
		
	}
	
	function get_where_clause($selected_filter=''){
		$return_string = " ";
		foreach ($this->filters_list as $filter){
			if ($filter['field_denomination'] != $selected_filter && $filter['selected'] == true){
				if ($filter['field_denomination'] == 'tipo_operacion'){
					$return_string .= 'AND tipo_operacion IN ("' . $filter['selected_value'] .'","")';
				}
				elseif ($filter['field_denomination'] == 'antiguedad'){
					switch ($filter['selected_value']){
						case 1 :  $return_string .= 'AND YEAR( CURDATE( ) ) - antiguedad = 0'; break;	
						case 2 :  $return_string .= 'AND YEAR( CURDATE( ) ) - antiguedad < 5'; break;	
						case 3 :  $return_string .= 'AND YEAR( CURDATE( ) ) - antiguedad < 11'; break;	
						case 4 :  $return_string .= 'AND YEAR( CURDATE( ) ) - antiguedad < 21'; break;	
						case 5 :  $return_string .= 'AND YEAR( CURDATE( ) ) - antiguedad < 31'; break;	
						case 6 :  $return_string .= 'AND YEAR( CURDATE( ) ) - antiguedad > 30'; break;	
					}
				}
				else{
					switch($filter['filter_type']){
						case 'text' 		: $return_string .= 'AND ' . $filter['id_field_on_table'] . ' like "%' . $filter['selected_value'] . '%" '; break;
						case 'items_list' 	: $return_string .= 'AND ' . $filter['id_field_on_table'] . ' = "' . $filter['selected_value'] . '" '; break;
						case 'range' 		: 
							if ($filter['selected_value']['min'] != ""){
								$return_string .= 'AND ' . $filter['id_field_on_table'] . ' >= ' . $filter['selected_value']['min'] . ' ';
							};
							if ($filter['selected_value']['max'] != ""){
								$return_string .= 'AND ' . $filter['id_field_on_table'] . ' <= ' . $filter['selected_value']['max'] . ' ';
							};
							break;
					}
				}
			}
		}
		$return_string .= ' AND ' . $this->campo_precio . ' != "" AND pa.id = ' . $this->filtros_obligatorios['pais'];
		if (!empty( $this->filtros_obligatorios['precio'])){
			$rango = $this->filtros_obligatorios['precio'];
			if ( strpos($rango,'-') ){
				$rango_splitted = explode('-',$rango);
				$minimo = $rango_splitted[0];
				$maximo = $rango_splitted[1];
				$return_string .= ' AND ' . 'precio >= ' . $minimo . ' AND precio <= ' . $maximo;
			}
			elseif( strpos($rango,'+') ){
				$minimo = substr($rango,0,strlen($rango)-1);
				$return_string .= ' AND ' . 'precio >= ' . $minimo;
			}
			else{
				$maximo = $rango;
				$return_string .= ' AND ' . 'precio <= ' . $maximo;
			}
		}
		return $return_string;
	}
	
	
	function get_display_text($denomination,$value){

		$return_string = '';
		switch ($denomination){
			case 'region' : 
				$query 				= 'SELECT descripcion FROM regiones WHERE id = "' . $value . '"';
				$nombre_region		= $this->db->getOne($query);
				$return_string 		= 'Region: ' . $nombre_region;
			break;
			case 'zona' : 
				$query 				= 'SELECT descripcion FROM zonas WHERE id = "' . $value . '"';
				$nombre_zona		= $this->db->getOne($query);
				$return_string 		= 'Zona: ' . $nombre_zona;

			break;
			case 'pais' : 
				$query 				= 'SELECT descripcion FROM paises WHERE id = "' . $value . '"';
				$nombre_pais		= $this->db->getOne($query);
				$return_string 		= $nombre_pais;
			break;
			case 'ambientes' : 
				$return_string 	= $value . ' ambiente';
				if ($value != '1'){ $return_string .= 's'; }
			break;
			case 'tipo' : 
				$query 				= 'SELECT descripcion FROM tipo_inmuebles WHERE id = "' . $value . '"';
				$nombre_inmueble	= $this->db->getOne($query);
				$return_string 		= $nombre_inmueble;
			break;
			case 'tipo_operacion' : 
				$return_string 	= ucfirst($value);
			break;
			case 'antiguedad' :
				switch ($value){
					case 1 :  $return_string .= 'A estrenar'; break;
					case 2 :  $return_string .= 'Hasta 5 años de antigüedad'; break;
					case 3 :  $return_string .= 'Hasta 10 años de antigüedad'; break;
					case 4 :  $return_string .= 'Hasta 20 años de antigüedad'; break;	
					case 5 :  $return_string .= 'Hasta 30 años de antigüedad'; break;
					case 6 :  $return_string .= 'Mas de 30 años de antigüedad'; break;
				}
			break;
		}
		return $return_string;
	}
	
	function get_parameters($string){
		// $exploded_string = explode('/',$string);
		// $return = array(
			// 'ciudad_codigo' => '',
			// 'categoria' 	=> '',
			// 'zona' 			=> ''
		// );
		// $return['ciudad_codigo'] = $exploded_string[1];
		// if (count($exploded_string) == 4){
			// $return['categoria'] 	= $exploded_string[2];
			// $return['zona'] 		= str_replace('-',' ',$exploded_string[3]);
		// }elseif(count($exploded_string) == 3) {
			// if ( is_numeric($exploded_string[2]) ){
				// $return['categoria'] 	= $exploded_string[2];
			// }else{
				// $return['zona'] 		= str_replace('-',' ',$exploded_string[2]);
			// }
		// }
		$return = array();
		return $return;
	}
	
	function get_filter_url($base_url,$invoker_filter,$invoker_value,$filtros_adicionales){
		$url = $base_url;
		$description_string = 'propiedades';
		
		$return = '/' . $url;
		
		if (!is_null($invoker_filter) && !is_null($invoker_value)){
			$return .= '/' . $invoker_filter . '/' . str_replace(' ','-',$invoker_value);
		}
		
		if ($filtros_adicionales != ""){
			$return .= '/' . $filtros_adicionales;
		}
		$return .= '/' . $description_string;
		
		if ($this->order_by != ""){
			$return .= '/order/' . $this->order_by;
		}
		return $return;
	}

	function get_items_list($filter){
		
		$where = $this->get_where_clause($filter['field_denomination']);
		
		if ($filter['field_denomination'] == 'antiguedad'){
								
			$basic_query = 
			'SELECT ' . $filter['id_field_on_table'] . ' as value,' . $filter['display_field_on_table'] . ' as display_value, count(distinct(p.id)) as cantidad, min(precio) as precio_minimo
								FROM propiedades p
								INNER JOIN zonas z ON p.zona_id = z.id
								INNER JOIN tipo_inmuebles ti ON p.tipo_id = ti.id
								INNER JOIN regiones r ON z.region_id = r.id
								INNER JOIN paises pa ON r.pais_id = pa.id
								WHERE 1 AND disponible  = "1" '.$where;
								
			/*
			'SELECT antiguedad AS value, antiguedad AS display_value, count( DISTINCT (
								p.id
								) ) AS cantidad, min( precio ) AS precio_minimo
								FROM propiedades p
								INNER JOIN zonas z ON p.zona_id = z.id
								INNER JOIN tipo_inmuebles ti ON p.tipo_id = ti.id
								WHERE ';
			*/
			$opciones = array(
							1 => array(
										'where'  => ' AND YEAR( CURDATE( ) ) - antiguedad = 0 ',
										'titulo' => 'A estrenar'
 									),
							2 => array(
										'where'  => ' AND YEAR( CURDATE( ) ) - antiguedad < 6',
										'titulo' => 'Hasta 5 años'
 									),
							3 => array(
										'where'  =>  ' AND YEAR( CURDATE( ) ) - antiguedad < 11',
										'titulo' => 'Hasta 10 años'
 									),
							4 => array(
										'where'  => ' AND YEAR( CURDATE( ) ) - antiguedad < 21',
										'titulo' => 'Hasta 20 años'
 									),
							5 => array(
										'where'  => ' AND YEAR( CURDATE( ) ) - antiguedad < 31',
										'titulo' => 'Hasta 30 años'
 									),
							6 => array(
										'where'  => ' AND YEAR( CURDATE( ) ) - antiguedad > 30',
										'titulo' => 'Mas de 30 años'
 									)
						);
						
			$result = array();
			foreach($opciones as $key => $cur_option){
				$query 		= $basic_query . $cur_option['where'];
				$cur_result = $this->db->getRow($query);
				$cur_result['value'] = $key;
				$cur_result['display_value'] = $cur_option['titulo'];
				$result[] = $cur_result;
			}
			
			// $used_ids = array();
			// for($i=1;$i<198;$i++){
				// $year = rand('1930','2013');
				// echo 'UPDATE propiedades SET antiguedad = ' . $year . ' WHERE id = ' . $i . ';<br>';
			// }
			// echo $query;
			// die();
		}
		else{
			$query = 'SELECT ' . $filter['id_field_on_table'] . ' as value,' . $filter['display_field_on_table'] . ' as display_value, count(distinct(p.id)) as cantidad, min(precio) as precio_minimo
								FROM propiedades p
								INNER JOIN zonas z ON p.zona_id = z.id
								INNER JOIN tipo_inmuebles ti ON p.tipo_id = ti.id
								INNER JOIN regiones r ON z.region_id = r.id
								INNER JOIN paises pa ON r.pais_id = pa.id
								WHERE 1 AND disponible  = "1" '.$where.'
								GROUP BY ' . $filter['id_field_on_table'] . '
								ORDER BY ' . $filter['id_field_on_table'];
			
			$result = $this->db->getAll($query);
			
		}
		
		if ($filter['field_denomination'] == 'tipo_operacion'){
			$x = 0;
			$propiedades_con_ambas_operaciones = 0;
			while ($x <= count($result)){
				if ( isset($result[$x]) ){
					if ($result[$x]['value'] == ''){
						$propiedades_con_ambas_operaciones += $result[$x]['cantidad'];
						unset($result[$x]);
					}
				}	
				$x++;
			}
			$x = 0;
			while ($x <= count($result)){
				if ( isset($result[$x]) ){
					if ($result[$x]['value'] != ''){
						$result[$x]['cantidad'] += $propiedades_con_ambas_operaciones;
					}
				}
				$x++;
			}			
		}
		return $result;
		
	}
	
	function get_display_value($denomination,$value,$display_value,$text){
		global $arrayMeses;
		switch ($denomination){
			case 'periodo' :
				$temp			= explode('-',$value);
				$return_string	= $arrayMeses[$temp[0]-1] . ' ' . $temp[1];
			break;
			case 'puerto' :
				$return_string = str_replace('<value>',ucfirst(strtolower($display_value)),$text);
			break;
			case 'ambientes' :
				$return_string = str_replace('<value>',ucfirst(strtolower($display_value)),$text);
				if ($display_value != 1){
					$return_string .= 's';
				}
			break;
			default:
				$return_string = str_replace('<value>',$display_value,$text);
			break;
		}
		return ucfirst($return_string);
	}	
	
	public function get_results(){

		$where = $this->get_where_clause();
		
		$orden = $this->order_by_def;
		if($this->order_by !=""){
			$order_tmp 	= explode('-',$this->order_by);
			if ($order_tmp[0] == 'precio'){
				$orden 		= 'precio ' . $order_tmp[1];  
			}
			else{
				$orden 		= $this->filters_list[$order_tmp[0]]['id_field_on_table'] . ' ' . $order_tmp[1];  
			}
		}
		

		
		$query = 	'SELECT p.id, 
							p.calle, 
							p.altura, 
							z.descripcion AS zona, 
							p.localidad, 
							p.provincia, 
							p.precio, 
							p.ambientes, 
							p.tipo_id, 
							p.metros,
							p.antiguedad,
							p.apto_prof,
							p.disposicion,
							p.cant_banos,
							p.cant_dormitorios,
							p.dependencia,
							ti.descripcion as tipo_inmueble,
							r.descripcion as region_nombre,
							pa.descripcion as pais_nombre
					FROM '.$this->tabla.'
					INNER JOIN zonas z ON p.zona_id = z.id
					INNER JOIN tipo_inmuebles ti ON p.tipo_id = ti.id
					INNER JOIN regiones r ON z.region_id = r.id
					INNER JOIN paises pa ON r.pais_id = pa.id
					WHERE 1 AND disponible  = "1" '.$where.'
					ORDER BY ' . $orden;
						
						
						
		$res	= $this->db->getAll($query);
		//echo '<pre>'; var_dump($where); echo '</pre>'; die();
		return $this->db->getAll($query);
	}
	
}


?>